package com.qulai.amazon_us.mapper.amazon_us;

import java.util.List;
import java.util.Map;

import com.qulai.amazon_us.domain.amazon_us.AmazonUsRecord;
import com.qulai.amazon_us.domain.amazon_us.bo.AmazonUsRecordExportBo;
import com.qulai.amazon_us.domain.amazon_us.dto.AmazonUsRecordDto;
import org.apache.ibatis.annotations.Delete;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;

/**
 * 记录明细Mapper接口
 * 
 * @author hunan
 * @date 2025-01-05
 */
public interface AmazonUsRecordMapper 
{
    /**
     * 查询记录明细
     * 
     * @param id 记录明细主键
     * @return 记录明细
     */
//    public AmazonUsRecord selectAmazonUsRecordById(Long id);



    /**
     * 查询记录明细
     *
     * @param  店铺名和主键 记录明细主键
     * @return 记录明细
     */

    @Select("<script>" +
            "SELECT id, status, sku, asin, basic_id, basic_platform, basic_sku, price, quantity, opt_name, brand, brand_zip, template_field, class_name, product_type, create_time, update_time, remark, update_by,upload_id, "+
            "  '${amazonUsRecord.shop}' AS shop "+
            "FROM ${recordTableName} " +

            "<where>" +
            "   <if test='amazonUsRecord.params != null and amazonUsRecord.params.id != null '> " +
            "        id =  #{amazonUsRecord.params.id} " +
            "   </if>" +

            "</where>" +

            "</script>")
    public AmazonUsRecord selectAmazonUsRecordByShopAndId(@Param("recordTableName") String recordTableName,
                                                          @Param("amazonUsRecord") AmazonUsRecord amazonUsRecord);


    /**
     * 查询记录明细
     *
     * @param asin 记录明细主键
     * @return 记录明细
     */
//    public AmazonUsRecord selectAmazonUsRecordByAsin(Map<String, Object> params);


    @Select("<script>" +
            "SELECT r.id, r.status, r.sku, r.asin, r.basic_id, r.basic_platform, r.basic_sku, r.price, m.amazon_quantity as quantity, " +
            "r.opt_name, r.brand, r.brand_zip, r.template_field, r.class_name, r.product_type, " +
            "r.create_time, r.update_time, r.remark, r.update_by, r.upload_id, " +
            "  '${params.shop}' AS shop " + // 使用参数中的 shop 值
            "FROM ${recordTableName} r " +
            "LEFT JOIN basic_product_map m ON r.basic_id = m.basic_id " +
            "<where>" +
            "   r.asin = #{params.asin} " + // 固定 asin 条件
            "</where>" +
            "LIMIT 1" + // 只查询一条记录
            "</script>")
    AmazonUsRecord selectAmazonUsRecordByAsin_v2(
            @Param("recordTableName") String recordTableName,
            @Param("params") Map<String, Object> params
    );


    /**
     * 查询记录明细列表
     * 
     * @param amazonUsRecord 记录明细
     * @return 记录明细集合
     */
//    public List<AmazonUsRecord> selectAmazonUsRecordList(AmazonUsRecord amazonUsRecord);

    /**
     * 查询记录明细列表
     *
     * @param amazonUsRecord 记录明细
     * @return 记录明细集合
     */
    @Select("<script>" +
            "SELECT id, status, sku, asin, basic_id, basic_platform, basic_sku, price, quantity, " +
            "       opt_name, brand, brand_zip, template_field, class_name, product_type, " +
            "       create_time, update_time, remark, update_by, upload_id,shop " +
            "FROM ( " +
            "   <foreach collection='shopList' item='shop' separator=' UNION ALL '> " +
            "       SELECT id, status, sku, asin, basic_id, basic_platform, basic_sku, price, quantity, " +
            "              opt_name, brand, brand_zip, template_field, class_name, product_type, " +
            "              create_time, update_time, remark, update_by, upload_id, " +
            "              REPLACE(#{shop}, 'amazon_us_record_', '') AS shop" +
            "       FROM ${shop} " +
            "<where>" +
            "   <if test='amazonUsRecord.status != null and amazonUsRecord.status.size() > 0'>" +
            "       AND status IN " +
            "       <foreach item='item' index='index' collection='amazonUsRecord.status' open='(' separator=',' close=')'>" +
            "           #{item}" +
            "       </foreach>" +
            "   </if>" +
            "   <if test='amazonUsRecord.sku != null and amazonUsRecord.sku != \"\"'> AND sku = #{amazonUsRecord.sku}</if>" +
            "   <if test='amazonUsRecord.asin != null and amazonUsRecord.asin != \"\"'> AND asin = #{amazonUsRecord.asin}</if>" +
            "   <if test='amazonUsRecord.basicId != null'> AND basic_id = #{amazonUsRecord.basicId}</if>" +
            "   <if test='amazonUsRecord.basicPlatform != null and amazonUsRecord.basicPlatform.size() > 0'>" +
            "       AND basic_platform IN " +
            "       <foreach item='item' index='index' collection='amazonUsRecord.basicPlatform' open='(' separator=',' close=')'>" +
            "           #{item}" +
            "       </foreach>" +
            "   </if>" +
            "   <if test='amazonUsRecord.basicSku != null and amazonUsRecord.basicSku != \"\"'> AND basic_sku = #{amazonUsRecord.basicSku}</if>" +
            "   <if test='amazonUsRecord.price != null and amazonUsRecord.price != \"\"'> AND price &gt;= #{amazonUsRecord.price}</if>" +
            "   <if test='amazonUsRecord.quantity != null and amazonUsRecord.quantity != \"\"'> AND quantity &gt;= #{amazonUsRecord.quantity}</if>" +
            "   <if test='amazonUsRecord.optName != null and amazonUsRecord.optName != \"\"'> AND opt_name = #{amazonUsRecord.optName}</if>" +
            "   <if test='amazonUsRecord.brand != null and amazonUsRecord.brand != \"\"'> AND brand = #{amazonUsRecord.brand}</if>" +
            "   <if test='amazonUsRecord.className != null and amazonUsRecord.className != \"\"'> AND class_name = #{amazonUsRecord.className}</if>" +
            "   <if test='amazonUsRecord.productType != null and amazonUsRecord.productType != \"\"'> AND product_type = #{amazonUsRecord.productType}</if>" +
            "   <if test='amazonUsRecord.params != null and amazonUsRecord.params.beginCreateTime != null and amazonUsRecord.params.endCreateTime != null'> " +
            "       AND create_time BETWEEN #{amazonUsRecord.params.beginCreateTime} AND #{amazonUsRecord.params.endCreateTime}" +
            "   </if>" +
            "   <if test='amazonUsRecord.updateBy != null and amazonUsRecord.updateBy != \"\"'> AND update_by = #{amazonUsRecord.updateBy}</if>" +
            "   <if test='amazonUsRecord.uploadId != null and amazonUsRecord.uploadId != \"\"'> AND upload_id = #{amazonUsRecord.uploadId}</if>" +
            "</where>" +
            "   </foreach> " +
            ") AS combined_results " +
            "ORDER BY create_time DESC " +
            "<if test='amazonUsRecord.offset != null and amazonUsRecord.limit != null'> " +
            "   LIMIT #{amazonUsRecord.offset}, #{amazonUsRecord.limit} " +
            "</if> " +
            "</script>")
    List<AmazonUsRecord> selectAmazonUsRecordList_v2(
            @Param("amazonUsRecord") AmazonUsRecordDto amazonUsRecord,
            @Param("shopList") List<String> shopList
    );

    @Select("<script>" +
            "SELECT  sku, basic_sku,product_type ,brand, create_time "+
            "FROM ${shop} " +
            "</script>")
    List<AmazonUsRecordExportBo> exportAmazonUsRecordList(
            @Param("amazonUsRecord") AmazonUsRecordDto amazonUsRecord,
            @Param("shop") String shop
    );


    /**
     * 上架明细数量统计
     *
     * @param amazonUsRecord 记录明细
     * @return 结果
     */

    @Select("<script>" +
            "SELECT COUNT(1) " +
            "FROM ( " +
            "   <foreach collection='shopList' item='shop' separator=' UNION ALL '> " +
            "       SELECT id, status, sku, asin, basic_id, basic_platform, basic_sku, price, quantity, " +
            "              opt_name, brand, brand_zip, template_field, class_name, product_type, " +
            "              create_time, update_time, remark, update_by, upload_id, " +
            "              REPLACE(#{shop}, 'amazon_us_record_', '') AS shop" +
            "       FROM ${shop} " +
            "<where>" +
            "   <if test='amazonUsRecord.status != null and amazonUsRecord.status.size() > 0'>" +
            "       AND status IN " +
            "       <foreach item='item' index='index' collection='amazonUsRecord.status' open='(' separator=',' close=')'>" +
            "           #{item}" +
            "       </foreach>" +
            "   </if>" +
            "   <if test='amazonUsRecord.sku != null and amazonUsRecord.sku != \"\"'> AND sku = #{amazonUsRecord.sku}</if>" +
            "   <if test='amazonUsRecord.asin != null and amazonUsRecord.asin != \"\"'> AND asin = #{amazonUsRecord.asin}</if>" +
            "   <if test='amazonUsRecord.basicId != null'> AND basic_id = #{amazonUsRecord.basicId}</if>" +
            "   <if test='amazonUsRecord.basicPlatform != null and amazonUsRecord.basicPlatform.size() > 0'>" +
            "       AND basic_platform IN " +
            "       <foreach item='item' index='index' collection='amazonUsRecord.basicPlatform' open='(' separator=',' close=')'>" +
            "           #{item}" +
            "       </foreach>" +
            "   </if>" +
            "   <if test='amazonUsRecord.basicSku != null and amazonUsRecord.basicSku != \"\"'> AND basic_sku = #{amazonUsRecord.basicSku}</if>" +
            "   <if test='amazonUsRecord.price != null and amazonUsRecord.price != \"\"'> AND price &gt;= #{amazonUsRecord.price}</if>" +
            "   <if test='amazonUsRecord.quantity != null and amazonUsRecord.quantity != \"\"'> AND quantity &gt;= #{amazonUsRecord.quantity}</if>" +
            "   <if test='amazonUsRecord.optName != null and amazonUsRecord.optName != \"\"'> AND opt_name = #{amazonUsRecord.optName}</if>" +
            "   <if test='amazonUsRecord.brand != null and amazonUsRecord.brand != \"\"'> AND brand = #{amazonUsRecord.brand}</if>" +
            "   <if test='amazonUsRecord.className != null and amazonUsRecord.className != \"\"'> AND class_name = #{amazonUsRecord.className}</if>" +
            "   <if test='amazonUsRecord.productType != null and amazonUsRecord.productType != \"\"'> AND product_type = #{amazonUsRecord.productType}</if>" +
            "   <if test='amazonUsRecord.params != null and amazonUsRecord.params.beginCreateTime != null and amazonUsRecord.params.endCreateTime != null'> " +
            "       AND create_time BETWEEN #{amazonUsRecord.params.beginCreateTime} AND #{amazonUsRecord.params.endCreateTime}" +
            "   </if>" +
            "   <if test='amazonUsRecord.updateBy != null and amazonUsRecord.updateBy != \"\"'> AND update_by = #{amazonUsRecord.updateBy}</if>" +
            "   <if test='amazonUsRecord.uploadId != null and amazonUsRecord.uploadId != \"\"'> AND upload_id = #{amazonUsRecord.uploadId}</if>" +
            "</where>" +
            "   </foreach> " +
            ") AS combined_results " +
            "ORDER BY create_time DESC " +
            "<if test='amazonUsRecord.offset != null and amazonUsRecord.limit != null'> " +
            "   LIMIT #{amazonUsRecord.offset}, #{amazonUsRecord.limit} " +
            "</if> " +
            "</script>")
    Long countAmazonUsRecordList_v2(
            @Param("amazonUsRecord") AmazonUsRecordDto amazonUsRecord,
            @Param("shopList") List<String> shopList
    );

    /**
     * 新增记录明细
     * 
     * @param amazonUsRecord 记录明细
     * @return 结果
     */
    public int insertAmazonUsRecord(AmazonUsRecord amazonUsRecord);

    /**
     * 修改记录明细
     * 
     * @param amazonUsRecord 记录明细
     * @return 结果
     */
    public int updateAmazonUsRecord(AmazonUsRecord amazonUsRecord);

    /**
     * 修改记录明细：分表之后的修改
     *
     * @param amazonUsRecord 记录明细
     * @return 结果
     */

    @Update("<script>" +
            "UPDATE ${recordTableName} " +
            "<trim prefix=\"SET\" suffixOverrides=\",\">" +
            "   <if test='amazonUsRecord.status != null'> status = #{amazonUsRecord.status},</if>" +
            "   <if test='amazonUsRecord.sku != null and amazonUsRecord.sku != \"\"'> sku = #{amazonUsRecord.sku},</if>" +
            "   <if test='amazonUsRecord.asin != null and amazonUsRecord.asin != \"\"'> asin = #{amazonUsRecord.asin},</if>" +
            "   <if test='amazonUsRecord.basicId != null'> basic_id = #{amazonUsRecord.basicId},</if>" +
            "   <if test='amazonUsRecord.basicPlatform != null and amazonUsRecord.basicPlatform != \"\"'> basic_platform = #{amazonUsRecord.basicPlatform},</if>" +
            "   <if test='amazonUsRecord.basicSku != null and amazonUsRecord.basicSku != \"\"'> basic_sku = #{amazonUsRecord.basicSku},</if>" +
            "   <if test='amazonUsRecord.price != null and amazonUsRecord.price != \"\"'> price = #{amazonUsRecord.price},</if>" +
            "   <if test='amazonUsRecord.quantity != null and amazonUsRecord.quantity != \"\"'> quantity = #{amazonUsRecord.quantity},</if>" +
            "   <if test='amazonUsRecord.optName != null and amazonUsRecord.optName != \"\"'> opt_name = #{amazonUsRecord.optName},</if>" +
            "   <if test='amazonUsRecord.brand != null and amazonUsRecord.brand != \"\"'> brand = #{amazonUsRecord.brand},</if>" +
            "   <if test='amazonUsRecord.brandZip != null and amazonUsRecord.brandZip != \"\"'> brand_zip = #{amazonUsRecord.brandZip},</if>" +
            "   <if test='amazonUsRecord.templateField != null and amazonUsRecord.templateField != \"\"'> template_field = #{amazonUsRecord.templateField},</if>" +
            "   <if test='amazonUsRecord.className != null and amazonUsRecord.className != \"\"'> class_name = #{amazonUsRecord.className},</if>" +
            "   <if test='amazonUsRecord.productType != null and amazonUsRecord.productType != \"\"'> product_type = #{amazonUsRecord.productType},</if>" +
            "   <if test='amazonUsRecord.createTime != null'> create_time = #{amazonUsRecord.createTime},</if>" +
            "   <if test='amazonUsRecord.updateTime != null'> update_time = #{amazonUsRecord.updateTime},</if>" +
            "   <if test='amazonUsRecord.remark != null and amazonUsRecord.remark != \"\"'> remark = #{amazonUsRecord.remark},</if>" +
            "   <if test='amazonUsRecord.updateBy != null and amazonUsRecord.updateBy != \"\"'> update_by = #{amazonUsRecord.updateBy},</if>" +
            "</trim>" +
            "WHERE id = #{amazonUsRecord.id}" +
            "</script>")
    public int updateAmazonUsRecord_v2(
            @Param("recordTableName") String recordTableName,
            @Param("amazonUsRecord") AmazonUsRecord amazonUsRecord);


    /**
     * 删除记录明细
     * 
     * @param id 记录明细主键
     * @return 结果
     */
    public int deleteAmazonUsRecordById(Long id);

    /**
     * 批量删除记录明细: 分表前
     * 
     * @param ids 需要删除的数据主键集合
     * @return 结果
     */
//    public int deleteAmazonUsRecordByIds(Long[] ids);

    /**
     * 批量删除记录明细: 分表后
     *
     * @param ids 需要删除的数据主键集合
     * @return 结果
     */

    @Delete("<script>" +
            "DELETE FROM ${recordTableName} " +
            "WHERE id IN " +
            "<foreach item='id' collection='ids' open='(' separator=',' close=')'>" +
            "   #{id}" +
            "</foreach>" +
            "</script>")
    int deleteAmazonUsRecordByIds_v2(
            @Param("recordTableName") String recordTableName,
            @Param("ids") Long[] ids
    );
/**
 *  定时任务绑定asin：分表前
 */

 //    @Select("select id,sku from amazon_us_record where shop = #{shop} and create_time <= #{intervalDayDateTimeStr} and asin =''")
//    public List<AmazonUsRecord> selectAmazonUsRecordListByShop(@Param("shop") String shop, @Param("intervalDayDateTimeStr") String intervalDayDateTimeStr);


/**
 *  定时任务绑定asin：查询1天前创建的并且未绑定asin的数据，分表后
 */

    @Select("select id,sku from ${recordTableName} where  create_time <= #{intervalDayDateTimeStr} and asin =''")
    public List<AmazonUsRecord> selectAmazonUsRecordListByShop_v2(@Param("recordTableName") String recordTableName, @Param("intervalDayDateTimeStr") String intervalDayDateTimeStr);



    @Select("SELECT EXISTS (SELECT 1 FROM information_schema.tables " +
            "WHERE table_schema = DATABASE() AND table_name = #{recordTableName})")
    public boolean tableExists(@Param("recordTableName") String recordTableName);


    /**
     *  定时任务绑定asin：更新asin值，分表前
     */
//    @Update("<script>" +
//            "UPDATE amazon_us_record " +
//            "SET asin = CASE " +
//            "<foreach collection='records' item='record' separator=''>" +
//            "    WHEN id = #{record.id} THEN #{record.asin} " +
//            "</foreach>" +
//            "END " +
//            "WHERE id IN " +
//            "<foreach collection='records' item='record' open='(' close=')' separator=','>" +
//            "    #{record.id} " +
//            "</foreach>" +
//            "</script>")
//    void batchUpdateAsin(@Param("records") List<AmazonUsRecord> records);

    @Update("<script>" +
            "UPDATE ${recordTableName} " +
            "SET asin = CASE " +
            "<foreach collection='records' item='record' separator=''>" +
            "    WHEN id = #{record.id} THEN #{record.asin} " +
            "</foreach>" +
            "END " +
            "WHERE id IN " +
            "<foreach collection='records' item='record' open='(' close=')' separator=','>" +
            "    #{record.id} " +
            "</foreach>" +
            "</script>")
    void batchUpdateAsin_v2(@Param("recordTableName") String recordTableName,@Param("records") List<AmazonUsRecord> records);

    /**
     * 根据平台查询亚马逊记录
     *
     * @param platform 平台代码
     * @return 记录列表
     */
//    @Select("SELECT id, basic_id, basic_sku, sku, price, quantity, shop, create_time FROM amazon_us_record WHERE basic_platform = #{platform}")
//    List<AmazonUsRecord> selectRecordsByPlatform(@Param("platform") String platform);

    /**
     * 批量更新记录的库存和价格
     *
     * @param records 记录列表，包含id, quantity, price
     * @return 更新结果
     */
    @Update("<script>" +
            "UPDATE amazon_us_record " +
            "SET quantity = CASE " +
            "<foreach collection='records' item='record' separator=''>" +
            "    WHEN id = #{record.id} THEN #{record.quantity} " +
            "</foreach>" +
            "END, " +
            "price = CASE " +
            "<foreach collection='records' item='record' separator=''>" +
            "    WHEN id = #{record.id} THEN #{record.price} " +
            "</foreach>" +
            "END " +
            "WHERE id IN " +
            "<foreach collection='records' item='record' open='(' close=')' separator=','>" +
            "    #{record.id} " +
            "</foreach>" +
            "</script>")
    int batchUpdateQuantityAndPrice(@Param("records") List<AmazonUsRecord> records);


}
